import type { PageResult } from '@renderer/common/domain'
import type { Server } from '../entity'
import type { ServerQuery } from '../domain'
import { v4 as uuidv4 } from 'uuid'

/**
 * 分页查询服务器列表
 * @param query 查询参数，支持按name模糊查询
 */
export async function getByPage(query: ServerQuery): Promise<PageResult<Server>> {
  const { page, pageSize, name } = query
  const offset = (page - 1) * pageSize

  const whereClause = name ? 'WHERE name LIKE ?' : ''
  const searchParams = name ? [`%${name}%`] : []

  // 获取总数
  const countSql = `SELECT COUNT(*) as total FROM servers ${whereClause}`
  const result = await window.db.get<{ total: number }>(countSql, searchParams)
  const total = result.total

  // 获取列表数据
  const listSql = `
    SELECT * FROM servers
    ${whereClause}
    ORDER BY update_time DESC
    LIMIT ? OFFSET ?
  `
  const list = await window.db.all<Server>(listSql, [...searchParams, pageSize, offset])

  return {
    list,
    total,
    page,
    pageSize
  }
}

/**
 * 创建服务器
 * @param server 服务器信息，不需要包含id和时间字段
 */
export async function add(server: Omit<Server, 'id' | 'createTime' | 'updateTime'>): Promise<void> {
  const now = new Date().toISOString()
  const id = uuidv4()

  await window.db.run(
    `INSERT INTO servers (id, name, host, port, username, password, description, create_time, update_time)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [id, server.name, server.host, server.port, server.username, server.password, server.description, now, now]
  )
}

/**
 * 更新服务器
 * @param server 服务器完整信息
 */
export async function update(server: Server): Promise<void> {
  const now = new Date().toISOString()

  await window.db.run(
    `UPDATE servers
     SET name = ?, host = ?, port = ?, username = ?, password = ?, description = ?, update_time = ?
     WHERE id = ?`,
    [server.name, server.host, server.port, server.username, server.password, server.description, now, server.id]
  )
}

/**
 * 删除服务器
 * @param id 服务器ID
 */
export async function remove(id: string): Promise<void> {
  await window.db.run('DELETE FROM servers WHERE id = ?', [id])
}

/**
 * 获取服务器详情
 * @param id 服务器ID
 */
export async function getById(id: string): Promise<Server | null> {
  return await window.db.get<Server | null>('SELECT * FROM servers WHERE id = ?', [id])
}

/**
 * 批量删除服务器
 * @param ids 服务器ID数组
 */
export async function batchRemove(ids: string[]): Promise<void> {
  const placeholders = ids.map(() => '?').join(',')
  await window.db.run(`DELETE FROM servers WHERE id IN (${placeholders})`, ids)
}

/**
 * 检查服务器名称是否已存在
 * @param name 服务器名称
 * @param excludeId 排除的服务器ID（用于更新时检查）
 */
export async function checkNameExists(name: string, excludeId?: string): Promise<boolean> {
  const sql = excludeId
    ? 'SELECT COUNT(*) as count FROM servers WHERE name = ? AND id != ?'
    : 'SELECT COUNT(*) as count FROM servers WHERE name = ?'
  const params = excludeId ? [name, excludeId] : [name]

  const result = await window.db.get<{ count: number }>(sql, params)
  return result.count > 0
}
